/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import controllers.Service;
import controllers.ExtMethods;
import java.text.SimpleDateFormat;
import java.util.Calendar;

/**
 *
 * @author Dan
 */
public class ServiceDAO extends DAOTest {
   
    public static Service getServiceByID(int serviceID) {
        Connection con = getConnection("Bunsen");
        Service returnService = null;

        try {
            Statement stmt = con.createStatement();
            String select = "SELECT "
                    + "activityid, "
                    + "[activity-name], "
                    + "begin_date,"
                    + "end_date, "
                    + "begin_time, "
                    + "end_time, "
                    + "address,"
                    + "travel_time, "
                    + "sun,"
                    + "mon,"
                    + "tue,"
                    + "wed,"
                    + "thurs,"
                    + "fri,"
                    + "sat,"
                    + "begin_date,"
                    + "end_date,"
                    + "transnumb "
                    + "FROM dbo.COMSERV_ACTIVITY WHERE activityid = " + serviceID +";";
            ResultSet rs = stmt.executeQuery(select);

            while (rs.next())
            {
                int id = rs.getInt("activityid");
                String name = rs.getString("activity-name");
                Time startTime = rs.getTime("begin_time");
                Time endTime = rs.getTime("end_time");
                int driveTime = rs.getInt("travel_time");
                String location = rs.getString("address");
                boolean sun = rs.getBoolean("sun");
                boolean mon = rs.getBoolean("mon");
                boolean tue = rs.getBoolean("tue");
                boolean wed = rs.getBoolean("wed");
                boolean thurs = rs.getBoolean("thurs");
                boolean fri = rs.getBoolean("fri");
                boolean sat = rs.getBoolean("sat");
                Date startDate = rs.getDate("begin_date");
                Date endDate = rs.getDate("end_date");
                int transnumb = rs.getInt("transnumb");
                returnService = new Service(id, name, startTime, endTime, driveTime, location,
                        sun, mon, tue, wed, thurs, fri, sat, startDate, endDate, transnumb);
            }
                
            con.close();
        }
        catch (SQLException e) {e.printStackTrace();}
        return returnService;
    }

    public static List<Service> getServicesByDay(Calendar calendar){

        String day = ExtMethods.getDayByNum(calendar.get(Calendar.DAY_OF_WEEK));
        Date dateLong = new Date(calendar.getTimeInMillis());
        SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy");
        String date = sdf.format(dateLong);
        Connection con = getConnection("Bunsen");
        ArrayList<Service> services = new ArrayList<Service>();

		try {
        	Statement stmt = con.createStatement();
        	String select = "SELECT + "
                    + "activityid, "
                    + "[activity-name], "
                    + "begin_date,"
                    + "end_date, "
                    + "begin_time, "
                    + "end_time, "
                    + "[address], "
                    + "travel_time, "
                    + "sun,"
                    + "mon,"
                    + "tue,"
                    + "wed,"
                    + "thurs,"
                    + "fri,"
                    + "sat,"
                    + "begin_date,"
                    + "end_date,"
                    + "transnumb "
                    + "FROM dbo.COMSERV_ACTIVITY WHERE "+day+"=1 and travel_time > 0 and maxstu > 0 "
                    + "and begin_date <= '" + date + "' and end_date >= '" + date
                    + "' ORDER BY begin_time;";
        	ResultSet rs = stmt.executeQuery(select);

            while (rs.next())
            {
                int id = rs.getInt("activityid");
                String name = rs.getString("activity-name");
                Time startTime = rs.getTime("begin_time");
                Time endTime = rs.getTime("end_time");
                int driveTime = rs.getInt("travel_time");
                String location = rs.getString("address");
                boolean sun = rs.getBoolean("sun");
                boolean mon = rs.getBoolean("mon");
                boolean tue = rs.getBoolean("tue");
                boolean wed = rs.getBoolean("wed");
                boolean thurs = rs.getBoolean("thurs");
                boolean fri = rs.getBoolean("fri");
                boolean sat = rs.getBoolean("sat");
                Date startDate = rs.getDate("begin_date");
                Date endDate = rs.getDate("end_date");
                int transnumb = rs.getInt("transnumb");
                services.add(new Service(id, name, startTime, endTime, driveTime, location,
                        sun, mon, tue, wed, thurs, fri, sat, startDate, endDate, transnumb));
            }

            con.close();


		} catch (SQLException e) {
			System.err.println(e.getMessage());e.printStackTrace();
		}
		return services;
    }

}
